-- extended event session to tune query performance
IF EXISTS (SELECT 1 FROM sys.server_event_sessions WHERE name = N'QueryTuning')
	DROP EVENT SESSION QueryTuning ON SERVER;
GO

CREATE EVENT SESSION QueryTuning ON SERVER
ADD EVENT sqlserver.rpc_completed(
	ACTION 
	(
		sqlserver.client_app_name,
		sqlserver.database_id,
		--sqlserver.database_name,
		sqlserver.nt_username,
		--sqlserver.server_principal_name,
		sqlserver.session_id,
		sqlserver.plan_handle,
		sqlserver.query_hash,
		sqlserver.query_hash_signed -- >= SQL Server 2014 SP2
	)
	WHERE (sqlserver.is_system = 0 AND sqlserver.database_id > 4)
		AND (logical_reads > 1000 OR cpu_time > 500 OR duration > 1000000)
),
ADD EVENT sqlserver.sp_statement_completed(
	ACTION 
	(
		sqlserver.client_app_name,
		sqlserver.database_id,
		--sqlserver.database_name,
		sqlserver.nt_username,
		--sqlserver.server_principal_name,
		sqlserver.session_id,
		sqlserver.plan_handle,
		sqlserver.query_hash,
		sqlserver.query_hash_signed -- >= SQL Server 2014 SP2
	)
	WHERE (sqlserver.is_system = 0 AND sqlserver.database_id > 4)
		AND (logical_reads > 1000 OR cpu_time > 500 OR duration > 1000000)
),
ADD EVENT sqlserver.sql_statement_completed(
	ACTION 
	(
		sqlserver.client_app_name,
		sqlserver.database_id,
		--sqlserver.database_name,
		sqlserver.nt_username,
		--sqlserver.server_principal_name,
		sqlserver.session_id,
		sqlserver.plan_handle,
		sqlserver.query_hash,
		sqlserver.query_hash_signed -- >= SQL Server 2014 SP2
	)
	WHERE (sqlserver.is_system = 0 AND sqlserver.database_id > 4)
		AND (logical_reads > 1000 OR cpu_time > 500 OR duration > 1000000)
)
ADD TARGET package0.event_file
(
	SET FILENAME = N'C:\Temp\QueryTuning.xel',
		MAX_FILE_SIZE = 200, MAX_ROLLOVER_FILES = 5
);
GO

-- enable event session
ALTER EVENT SESSION QueryTuning ON SERVER STATE = START;
GO

-- monitor the workload

-- stop the event session
ALTER EVENT SESSION QueryTuning ON SERVER STATE = STOP;
GO

-- query target data
SELECT 
	event_data.value('(event/@timestamp)[1]','datetime2') AS 'timestamp',
	event_data.value('(event/action[@name="database_name"])[1]', 'nvarchar(128)') AS database_name,
	event_data.value('(event/action[@name="session_id"])[1]','int') AS session_id,
	CAST(event_data.value('(event/data[@name="duration"])[1]','int')/1000.0 AS decimal(12,2)) AS duration_ms,
	CAST(event_data.value('(event/data[@name="cpu_time"])[1]','int')/1000.0 AS decimal(12,2)) AS cpu_time_ms,
	event_data.value('(event/data[@name="physical_reads"])[1]','int') AS physical_reads,
	event_data.value('(event/data[@name="logical_reads"])[1]','int') AS logical_reads,
	event_data.value('(event/data[@name="writes"])[1]','int') AS writes,
	event_data.value('(event/data[@name="row_count"])[1]','int') AS row_count,
	event_data.value('(event/@name)[1]', 'nvarchar(128)') AS event_name,
	event_data.value('(event/action[@name="nt_username"])[1]', 'nvarchar(128)') AS nt_user_name,
	event_data.value('(event/action[@name="server_principal_name"])[1]', 'nvarchar(128)') AS server_principal_name,
	event_data.value('(event/action[@name="client_app_name"])[1]', 'nvarchar(128)') AS client_app_name,
	event_data.value('(event/data[@name="statement"])[1]', 'nvarchar(max)') AS 'statement',
	event_data.value('(event/data[@name="batch_text"])[1]', 'nvarchar(max)') AS batch_text,
	event_data.value('(event/action[@name="plan_handle"])[1]', 'varbinary(64)') AS plan_handle,
	event_data.value('(event/action[@name="query_hash"])[1]', 'binary(8)') AS query_hash,
	event_data.value('(event/action[@name="query_hash_signed"])[1]', 'bigint') AS query_hash_signed,
	event_data
FROM
(
	SELECT
		CAST(event_data AS XML) AS event_data
	FROM sys.fn_xe_file_target_read_file
	-- SQL 2008 (R2)
	-- (N'C:\Temp\QueryTuning*.xel', N'C:\Temp\QueryTuning*.xem', NULL, NULL)
	-- SQL 2012 no metadata file required
	(N'C:\Temp\QueryTuning*xel', NULL, NULL, NULL)
) AS tab
GO

-- get the query plan from the plan_handle
-- http://www.sqlskills.com/blogs/jonathan/what-plan_handle-is-extended-events-sqlserver-plan_handle-action-returning/

DECLARE @plan_handle varbinary(64) = 0x<plan_handle>;
SELECT * FROM sys.dm_exec_query_plan(@plan_handle);

-- get the query stats and the cached plan from the query_hash_signed
-- https://blogs.msdn.microsoft.com/sql_server_team/correlating-xevents-with-dmvs/

SELECT * FROM sys.dm_exec_query_stats
WHERE CAST(query_plan_hash AS BIGINT) = <query_hash_signed>;

SELECT * FROM sys.dm_exec_query_stats
CROSS APPLY sys.dm_exec_query_plan(plan_handle)
WHERE CAST(query_plan_hash AS BIGINT) = <query_hash_signed>;

-- cleanup
DROP EVENT SESSION QueryTuning ON SERVER;
GO